package com.xiguaji.tiktok.v1.ins.blogger.repository;

import com.xiguaji.tiktok.v1.ins.blogger.dto.*;
import com.xiguaji.tiktok.v1.ins.blogger.model.InsBlogger;
import org.springframework.data.jdbc.repository.query.Query;
import org.springframework.data.repository.CrudRepository;

import java.util.List;
import java.util.Optional;


public interface InsBloggerRepository extends CrudRepository<InsBlogger,Integer>,InsBloggerExtRepository {
    /**
     * 根据uid 获取 粉丝兴趣品牌
     * @param uid
     * @param start
     * @param size
     * @return
     */
    @Query("select t1.brand_uid uid,t2.username,t2.pic_url,t2.overall_category_name,t2.category_name from " +
            "(select brand_uid from ins_st_blogger_fl_brand where uid = :uid order by brand_rank limit :start, :size )t1 " +
            " inner join ins_blogger t2 " +
            " on t1.brand_uid = t2.uid ")
    List<InsBloggerSimple> findBrandByUid(long uid,int start,int size);

    /**
     * 根据username 获取 用户基本信息
     * @param username
     * @return
     */
    @Query("select t1.*,dic.`name` nation_name,brf.real_follower_count " +
            " from (select uid,username,full_name,avg_er * 100 avg_er,follower_count,nation,last_follower_grow,pic_url,overall_category_name," +
            " category_name,fbid,contact_phone_number,public_email,public_phone_country_code,biography," +
            " case when last_collect_time = 0 then create_time else last_collect_time end last_collect_time ," +
            " public_phone_number,is_verified,is_business,create_time from ins_blogger_main where username = :username) t1 " +
            " left join ins_dic_nation dic on dic.`code` = t1.nation " +
            " left join ins_st_blogger_real_following brf on brf.uid = t1.uid ")
    Optional<InsBloggerBaseInfo> findInsBloggerInfoByUid(String username);

    /**
     * 根据uid 获取 粉丝概况
     * @param uid
     * @return
     */
    @Query("select t1.uid,t1.real_follower_rate,t1.gender,t2.race,t3.nation,t4.language_name `language`,t5.generation age " +
            " from " +
            " (select uid,real_follower_rate  * 100 real_follower_rate," +
            " case when female_rate is null then null when female_rate >= 0.5 then 'Female' else 'Male' end gender " +
            " from ins_st_blogger_real_following where uid = :uid )t1 " +
            " left join (select uid, race from ins_st_blogger_race where uid = :uid and race_rank = 1) t2 " +
            " on t1.uid = t2.uid " +
            " left join " +
            "(select nat.uid, dic.`name` nation from " +
            "(select uid, nation from ins_st_blogger_nation  where uid = :uid and nation_rank = 1) nat " +
            " left join ins_dic_nation dic on dic.`code` = nat.nation)t3 " +
            " on t1.uid = t3.uid " +
            " left join " +
            "(select lan.uid,dicl.`name` language_name from " +
            "(select uid,`language` from ins_st_blogger_language  where uid = :uid and language_rank = 1) lan " +
            " left join ins_dic_language dicl on dicl.`code` = lan.`language`)t4 " +
            " on t1.uid = t4.uid " +
            " left join " +
            "(select uid,generation from ins_st_blogger_generation where uid = :uid and generation_rank = 1)t5 " +
            "on t1.uid = t5.uid ")
    Optional<InsBloggerFansOverview> findFansOverviewByUid(long uid);

    /**
     * 根据uid 获取 粉丝质量基本信息
     * @param uid
     * @return
     */
    @Query("select t1.uid,t1.real_follower_count real_follower,t1.real_follower_rate,t1.gender,t1.gender_rate, " +
            "t2.nation_rate,t2.nation,t3.age,t3.age_rate " +
            " from " +
            "(select uid,real_follower_count,real_follower_rate * 100 real_follower_rate ,adult_rate," +
            " case when female_rate is null then null when female_rate >= 0.5 then 'Female' else 'Male' end gender,  " +
            " case when female_rate is null then 0  when female_rate < 0.5 then (1-female_rate) * 100 else female_rate * 100 end gender_rate " +
            " from ins_st_blogger_real_following where uid = :uid)t1 " +
            "left join  " +
            "(select uid, nation,nation_rate * 100 nation_rate from ins_st_blogger_nation where uid = :uid and nation_rank = 1)t2 " +
            "on t1.uid = t2.uid " +
            "left join " +
            "(select uid,generation age,generation_rate * 100 age_rate from ins_st_blogger_generation where uid = :uid and generation_rank = 1)t3 " +
            "on t1.uid = t3.uid ")
    Optional<InsBloggerFansGoalResponse> findFansQualityBaseInfoByUid(long uid);

    /**
     * 根据uid 获取 粉丝年龄段
     * @param uid
     * @return
     */
    @Query("select generation ,generation_rank,generation_rate * 100 generation_rate,female_rate * 100 female_rate,male_rate * 100 male_rate " +
            " from ins_st_blogger_generation where uid = :uid order by generation ")
    List<InsBloggerFansGeneration> findFansGenerationByUid(long uid);

    /**
     * 根据uid 获取 粉丝收入
     * @param uid
     * @return
     */
    @Query("select UPPER(t1.income)income ,t1.income_rank,t1.income_rate from " +
            "(select income ,income_rank,income_rate * 100 income_rate from ins_st_blogger_income where uid = :uid ) t1 " +
            "inner join ins_dic_income t2 " +
            "on t1.income = t2.income " +
            "order by t2.id ")
    List<InsBloggerFansIncome> findFansIncomeByUid(long uid);

    /**
     * 根据uid 获取 粉丝人种
     * @param uid
     * @param start
     * @param size
     * @return
     */
    @Query("select race race_name,race_rate * 100 race_rate from ins_st_blogger_race where uid = :uid order by race_rank limit :start ,:size  ")
    List<InsBloggerFansRace> findFansRaceByUid(long uid ,int start, int size);

    /**
     * 根据uid 获取 粉丝国家
     * @param uid
     * @param start
     * @param size
     * @return
     */
    @Query("select dic.`name` nation_name,t1.nation_rate from " +
            "(select nation,nation_rate * 100 nation_rate,nation_rank from ins_st_blogger_nation where uid = :uid  " +
            " limit :start ,:size  ) t1 " +
            "left join ins_dic_nation dic  " +
            "on dic.`code` = t1.nation order by t1.nation_rank")
    List<InsBloggerFansNation> findFansNationByUid(long uid,int start, int size);

    /**
     * 根据uid 获取 粉丝语言
     * @param uid
     * @param start
     * @param size
     * @return
     */
    @Query("select dic.`name` language_name,t1.language_rate from " +
            "(select `language`,language_rate * 100 language_rate,language_rank from ins_st_blogger_language where uid = :uid " +
            " limit :start ,:size ) t1 " +
            "left join ins_dic_language dic " +
            "on dic.`code` = t1.`language` order by t1.language_rank")
    List<InsBloggerFansLanguage> findFansLanguageByUid(long uid,int start, int size);

    /**
     * 根据uid 获取 粉丝性别,成人占比
     * @param uid
     * @return
     */
    @Query("select adult_rate * 100 adult_rate,case when female_rate is null then null else female_rate * 100 end  female_rate," +
            " case when female_rate is null then null else (1-female_rate) * 100 end male_rate " +
            " from ins_st_blogger_real_following where uid = :uid ")
    Optional<InsBloggerFansGenderAdult> findFansGenderByUid(long uid);

    /**
     * 根据uid 获取 账户得分
     * @param uid
     * @return
     */
    @Query("select tt1.*,tt2.id real_follower,tt3.id avg_er,tt4.id ads_performance,tt5.id follower_growth from " +
            "(select IFNULL(t2.real_follower_rate,0)real_follower_rate,IFNULL(t3.media_er,0) media_er,IFNULL(t4.ad_media_er,0) " +
            " ad_media_er,IFNULL(t5.follower_growth_rate,0) follower_growth_rate," +
            " t1.real_follower_rate rfr,t1.avg_er mer,t1.brand_media_er amer,t1.last_follower_grow_rate fgr from " +
            "(select uid,follower_size,overall_category_name,real_follower_rate * 10000 real_follower_rate, avg_er * 10000 avg_er," +
            " brand_media_er * 10000 brand_media_er,last_follower_grow_rate* 10000 last_follower_grow_rate from ins_blogger_main where uid = :uid) t1 " +
            "left join ins_st_blogger_cfs t2 " +
            "on t1.follower_size = t2.follower_size and t1.overall_category_name = t2.category and t2.rate >= t1.real_follower_rate " +
            "and t2.rate < t1.real_follower_rate + 5 " +
            "left join ins_st_blogger_cfs t3 " +
            "on t1.follower_size = t3.follower_size and t1.overall_category_name = t3.category and t3.rate >= t1.avg_er " +
            "and t3.rate < t1.avg_er  + 5 " +
            "left join ins_st_blogger_cfs t4 " +
            "on t1.follower_size = t4.follower_size and t1.overall_category_name = t4.category and t4.rate >= t1.brand_media_er " +
            "and t4.rate < t1.brand_media_er  + 5 " +
            "left join ins_st_blogger_cfs t5 " +
            "on t1.follower_size = t5.follower_size and t1.overall_category_name = t5.category and t5.rate >= t1.last_follower_grow_rate " +
            "and t5.rate < t1.last_follower_grow_rate  + 5) tt1 " +
            "left join ins_dic_appraisal tt2 " +
            "on tt2.appraisal_start <= tt1.real_follower_rate and tt2.appraisal_end > tt1.real_follower_rate " +
            "left join ins_dic_appraisal tt3 " +
            "on tt3.appraisal_start <= tt1.media_er and tt3.appraisal_end > tt1.media_er " +
            "left join ins_dic_appraisal tt4 " +
            "on tt4.appraisal_start <= tt1.ad_media_er and tt4.appraisal_end > tt1.ad_media_er " +
            "left join ins_dic_appraisal tt5 " +
            "on tt5.appraisal_start <= tt1.follower_growth_rate and tt5.appraisal_end > tt1.follower_growth_rate")
    Optional<InsUserGoal> findUserGoalByUid(long uid);

    /**
     * 根据uid 获取 账户近30天粉丝增长数、同分类同量级平均值、超越人数百分比
     * @param uid
     * @return
     */
    @Query("select t2.follower_growth_rate exceed,t1.grow_rate rate,t3.follower_growth_rate * 100 same_cfs_rate from " +
            "(select uid,follower_size,overall_category_name,last_follower_grow_rate * 10000 last_follower_grow_rate," +
            " last_follower_grow_rate * 100 grow_rate from ins_blogger_main where uid = :uid) t1 " +
            "left join ins_st_blogger_cfs t2 " +
            "on t1.follower_size = t2.follower_size and t1.overall_category_name = t2.category and t2.rate >= t1.last_follower_grow_rate " +
            "and t2.rate < t1.last_follower_grow_rate + 5 " +
            "left join ins_st_blogger_cfs_avg t3 " +
            "on t1.follower_size = t3.follower_size and t1.overall_category_name = t3.category ")
    Optional<InsBloggerCfsData> findSaneCfsFollowerGrowthByUid(long uid);

    /**
     * 根据uid 获取 账户真实粉丝占比、同分类同量级平均值、超越人数百分比
     * @param uid
     * @return
     */
    @Query("select t2.real_follower_rate exceed,t1.rate,t3.real_follower_rate * 100 same_cfs_rate from " +
            "(select uid,follower_size,overall_category_name,real_follower_rate * 10000 exceed,real_follower_rate * 100 rate " +
            " from ins_blogger_main where uid = :uid) t1 " +
            "left join ins_st_blogger_cfs t2 " +
            "on t1.follower_size = t2.follower_size and t1.overall_category_name = t2.category and t2.rate >= t1.exceed " +
            "and t2.rate < t1.exceed + 5 " +
            "left join ins_st_blogger_cfs_avg t3 " +
            "on t1.follower_size = t3.follower_size and t1.overall_category_name = t3.category")
    Optional<InsBloggerCfsData> findSaneCfsRealFollower(long uid);

    /**
     * 根据 uid 获取 排名
     * @param uid
     * @return
     */
    @Query("SELECT t1.category_name, t2.`name` nation, t1.rank " +
            " FROM ( SELECT t.category_name, t.nation, t.rank FROM ins_rk_blogger_fl_month t " +
            " WHERE uid = :uid AND datecode = (select max(datecode) from ins_rk_blogger_fl_month) ) t1 " +
            " LEFT JOIN ins_dic_nation t2 ON t1.nation = t2.`code`")
    List<InsBloggerRank> findInsBloggerRankByUid(long uid);

    Optional<InsBlogger> findByUid(long uid);


    @Query("select count(*) posts,IFNULL(ROUND(avg(m.video_view_count),1),0) avg_view,IFNULL(ROUND(avg(m.like_count),1),0) avg_likes,IFNULL(ROUND(avg(m.comment_count),1),0)avg_reviews, IFNULL(avg(m.er),0) avg_er from ins_rel_media_product mp left join ins_media m on mp.shortcode = m.shortcode " +
            "where mp.uid = :uid and mp.taken_time between :startDate and :endDate")
    InsBloggerProductMediaSt getMediaStForTagProductByUid(long uid, long startDate, long endDate);


    @Query("select count(*) posts,IFNULL(ROUND(avg(m.video_view_count),1),0) avg_view,IFNULL(ROUND(avg(m.like_count),1),0) avg_likes,IFNULL(ROUND(avg(m.comment_count),1),0)avg_reviews, IFNULL(avg(m.er),0) avg_er  from ins_rel_media_tagger_user mtu " +
            "left join ins_media m on mtu.shortcode = m.shortcode " +
            "where mtu.tagger_uid = :uid and mtu.taken_time between :startDate and :endDate")
    TaggedMediaCountResp findTaggerBloggerCountByUid(long uid, long startDate, long endDate);

    /**
     * 标记些博主的博主分类统计
     * @param uid
     * @param startTime
     * @param endTime
     * @return
     */
    @Query("select count(*) count,b.overall_category_name category from " +
            "ins_rel_media_tagger_user mtu left join ins_blogger_main b on mtu.uid = b.uid " +
            "where mtu.tagger_uid = :uid and mtu.taken_time between :startTime and :endTime group by category order by count desc")
    List<InsBloggerCategoryStatistics> findCountTaggerCategory(long uid, long startTime, long endTime);
}
